﻿DECLARE @Var_MZNum VARCHAR(100)
DECLARE @Var_IsLastWeekFlag INT
DECLARE @Var_RecipeNum VARCHAR(100)

SET @Var_MZNum = @MZNum_In
SET @Var_ISLASTWEEKFLAG = 1
SET @Var_RecipeNum = @RecipNum_In

SELECT  a.KEYNO AS Id ,
        RTRIM(b.PATIENTID) AS PatientId ,
        b.MZNUM AS MZNum ,
        b.CFNUM AS RecipeId ,
        RTRIM(b.YSKSCODE) AS ExeDeptId ,
        RTRIM(b.YSKSNAME) AS ExeDeptName ,
        RTRIM(b.YSKSCODE) AS DoctDeptId ,
        RTRIM(b.YSKSNAME) AS DoctDeptName ,
        RTRIM(b.YSCODE) AS DoctId ,
        RTRIM(b.YSNAME) AS DoctName ,
        b.CFMONEY AS RecipeAmount ,
        b.CFDATE AS RecipeDate ,
        RTRIM(b.Note) AS Note ,
        RTRIM(a.GOODSNO) AS ItemId ,
        RTRIM(a.GOODSNAME) AS ItemName ,
        RTRIM(a.KMNAME) AS ItemType ,
        RTRIM(a.UNITNAME) AS Unit ,
        a.YPPRICE AS Price ,
        a.TotCount * a.CFCOUNT AS Quantity ,
        a.YPMONEY AS Amount ,
        a.TotCount AS QUYI_Quantity ,
        a.CFCOUNT AS CFCount ,
        0 AS Flag ,
        CASE WHEN b.CFTYPE = 1 THEN '急诊处方'
             WHEN b.CFTYPE = 2 THEN '儿科处方'
             WHEN b.CFTYPE = 3 THEN '第二类精神处方'
             WHEN b.CFTYPE = 4 THEN '第一类精神处方'
             WHEN b.CFTYPE = 5 THEN '特殊疾病(门诊大病)处方'
             WHEN b.CFTYPE = 6 THEN '慢性病门诊处方'
             WHEN b.CFTYPE = 7 THEN '毒麻处方'
             ELSE '普通处方'
        END AS CFName
FROM    MZCFYPK_YZ a ( NOLOCK ) ,
        MZCFINF_YZ b ( NOLOCK )
WHERE   a.CFNUM = b.CFNUM
        AND a.MZNUM = b.MZNUM
        AND b.MZOPERDATE IS NULL
        AND CONVERT(VARCHAR(20), b.MZNUM) LIKE '%' + @Var_MZNum
        AND CONVERT(VARCHAR(20), b.CFNUM) LIKE '%' + @Var_RecipeNum
        AND ( ( @Var_IsLastWeekFlag ) = 0 OR ( ( @Var_IsLastWeekFlag ) = 1 AND b.CFDATE > GETDATE() - 3))
UNION ALL
SELECT  a.KEYNO AS Id ,
        RTRIM(b.PATIENTID) AS PatientId ,
        b.MZNUM AS MZNum ,
        b.APPLYNUM + 1000000000 AS RecipeId ,
        RTRIM(b.FSKSCODE) AS ExeDeptId ,
        RTRIM(b.FSKSNAME) AS ExeDeptName ,
        RTRIM(b.YSKSCODE) AS DoctDeptId ,
        RTRIM(b.YSKSNAME) AS DoctDeptName ,
        RTRIM(b.YSCODE) AS DoctId ,
        RTRIM(b.YSNAME) AS DoctName ,
        b.CHECKMONEY AS RecipeAmount ,
        b.APPLYDATE AS RecipeDate ,
        RTRIM(b.NOTE) AS Note ,
        RTRIM(a.CHECKNO) AS ItemId ,
        RTRIM(a.CHECKNAME) AS ItemName ,
        RTRIM(a.KMNAME) AS ItemType ,
        RTRIM(a.UNITNAME) AS Unit ,
        a.CHECKPRICE AS Price ,
        a.CHECKCOUNT AS Quantity ,
        a.CHECKPRICE AS Amount ,
        a.CHECKCOUNT AS QUYI_Quantity ,
        a.CHECKCOUNT AS CFCount ,
        1 AS Flag ,
        a.ApplicationName AS CFName
FROM    Patient_ApplicationCheckCode a ( NOLOCK ) ,
        Patient_ApplicationSheet b ( NOLOCK )
WHERE   a.APPLYNUM = b.APPLYNUM
        AND JZOPER IS NULL
        AND b.MZNUM LIKE '%' + @Var_MZNum
        AND CONVERT(VARCHAR(50), ( b.APPLYNUM + 1000000000 )) LIKE +'%'+ @Var_RecipeNum
        AND ( ( @Var_IsLastWeekFlag ) = 0 OR ( ( @Var_IsLastWeekFlag ) = 1 AND b.APPLYDATE > GETDATE() - 3))
UNION ALL
SELECT  0 AS Id ,
        PatientID AS PatientId ,
        MZNum AS MZNum ,
        MZNum + 2000000000 AS RecipeId ,
        KSCode AS ExeDeptId ,
        KSName AS ExeDeptName ,
        KSCode AS DoctDeptId ,
        KSName AS DoctDeptName ,
        YSCode AS DoctId ,
        YSName AS DoctName ,
        XMMoney AS RecipeAmount ,
        RegisterDate AS RecipeDate ,
        '补交挂号费' AS ItemType ,
        XMCode AS ItemId ,
        XMName AS ItemName ,
        NULL AS ItemType ,
        '次' AS Unit ,
        XMMoney AS Price ,
        1 AS Quantity ,
        XMMoney AS Amount ,
        1 AS QUYI_Quantity ,
        1 AS CFCount ,
        -1 AS Flag ,
        '后缴挂号费' AS CFName
FROM    MZRegisterSheet (NOLOCK)
WHERE   CONVERT(VARCHAR(50), ( MZNum + 2000000000 )) LIKE + '%' + @Var_RecipeNum
        AND MZNum LIKE '%' + @Var_MZNum
        AND FPNum IS NULL
        AND DelDate IS NULL
        AND XMMoney <> 0
        AND RegisterDate >= '2019-01-01'
ORDER BY Flag ,
        RecipeId ,
        keyno;